package henu.dao.impl;

import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import henu.bean.BusinessMessage;
import henu.bean.Car;
import henu.bean.Notice;
import henu.dao.BusinessMessageDao;
import henu.util.Dbcp;

public class BusinessMessageDaoImpl implements BusinessMessageDao {

	@Override
	public List<BusinessMessage> findAllByCarId(String property, String key,String idCard,
			String order, String sort,int start,int end) {
		// TODO Auto-generated method stub
		List<BusinessMessage> list=null;
		try {
			QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
			String sql="SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (select bm.* from businessmessage bm,carinformation c where "+property+"='"+key+"' and c.userid=? and c.carid=bm.carid order by "+order+" "+sort+") tt WHERE ROWNUM <="+end+") WHERE ro > "+start+"";
			list=qr.query(sql, new BeanListHandler<BusinessMessage>(BusinessMessage.class),idCard);
		} catch (Exception e) {
			// TODO: handle exception
		}
		return list;
	}
	@Override
	public List<BusinessMessage> AdminFindAllByComid(String property,
			String key, int comid, String order, String sort,
			int start, int end) {
		List<BusinessMessage> list=null;
		try {
			QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
			String sql="SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (select bm.* from businessmessage bm,carinformation c where "+property+"='"+key+"' and c.comid=? and c.carid=bm.carid order by "+order+" "+sort+") tt WHERE ROWNUM <="+end+") WHERE ro > "+start+"";
			list=qr.query(sql, new BeanListHandler<BusinessMessage>(BusinessMessage.class),comid);
		} catch (Exception e) {
			// TODO: handle exception
		}
		return list;
	}
	@Override
	public Car findByIdCardAndVehicleLicense(String idCard,
			String vehicleLicense) {
		// TODO Auto-generated method stub
		Car c=null;
		try {
			QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
			String sql="select * from carinformation  where userid=? and vehicleLicense=?";
			Object[] params={idCard,vehicleLicense};
			c=qr.query(sql, new BeanHandler<Car>(Car.class),params);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return c;
	}

	@Override
	public BusinessMessage findByBusinessIdOrVehicleLicense(String businessId,
			String vehicleLicense) {
	
		// TODO Auto-generated method stub
		BusinessMessage bm=null;
		try {
			QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
			String sql="select * from businessmessage  where businessId=? or vehicleLicense=?";
			Object[] params={businessId,vehicleLicense};
			bm=qr.query(sql, new BeanHandler<BusinessMessage>(BusinessMessage.class),params);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return bm;
	}
	
	@Override
	public int add(BusinessMessage bm) {
		int result=0;
		try {
			QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
			String sql="insert into businessmessage(businessId,masterName,address,"
					+ "carid,vehicleLicense,businessPermission,seats,carType"
					+ ",carSize,businessRage,publishOffice,publishDate"
					+ ",validityBegin,validityEnd,businessFile,memo) "
					+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			Object[] params={bm.getBusinessId(),bm.getMasterName(),bm.getAddress(),
					bm.getCarid(),bm.getVehicleLicense(),bm.getBusinessPermission(),bm.getSeats(),bm.getCarType(),
					bm.getCarSize(),bm.getBusinessRage(),bm.getPublishOffice(),bm.getPublishDate(),
					bm.getValidityBegin(),bm.getValidityEnd(),bm.getBusinessFile(),bm.getMemo()};
			result=qr.update(sql, params);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public int delete(int bid) {
		int result=0;
		try {
			QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
			String sql="delete from businessmessage where bid=?";
			result=qr.update(sql, bid);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public BusinessMessage find(int  bid) {
		// TODO Auto-generated method stub
		BusinessMessage bm=null;
		try {
			QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
			String sql="select * from businessmessage where bid=?";
			bm=qr.query(sql, new BeanHandler<BusinessMessage>(BusinessMessage.class), bid);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return bm;
	}

	@Override
	public int update(BusinessMessage bm) {
		// TODO Auto-generated method stub
		int result=0;
		try {
			QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
			String sql="update businessmessage set businessId=?,masterName=?,address=?,"
					+ "seats=?,carType=?,businessPermission=?,businessFile=?"
					+ ",carSize=?,businessRage=?,publishOffice=?,publishDate=?"
					+ ",validityBegin=?,validityEnd=?,memo=? where bid=?";
			Object[] params={bm.getBusinessId(),bm.getMasterName(),bm.getAddress(),
					bm.getSeats(),bm.getCarType(),bm.getBusinessPermission(),bm.getBusinessFile(),
					bm.getCarSize(),bm.getBusinessRage(),bm.getPublishOffice(),bm.getPublishDate(),
					bm.getValidityBegin(),bm.getValidityEnd(),bm.getMemo(),bm.getBid()};
			result=qr.update(sql, params);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return result;
	}

	
}
